package easyDAO;

import com.alibaba.fastjson.JSONObject;
import models.MyConfig;
import models.SqlReturnModel;
import tools.OperateSql;

import java.util.ArrayList;
import java.util.Map;
import java.util.Objects;

/**
 * Created by yangxianming on 2017/6/15.
 * 数据库操作
 */
public class SqlDispatch {
    private MyConfig myConfig;
    OperateSql operateSqlFirst;

    public SqlDispatch(MyConfig myConfig){
        this.myConfig = myConfig;
    }
    //可以先建立链接
    public SqlDispatch(MyConfig myConfig, String non){
        this.myConfig = myConfig;
        operateSqlFirst = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSqlFirst.createConnection();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public SqlReturnModel insert(String sqlWord){
        OperateSql operateSql = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSql.createConnection();
        }catch (Exception e){
            e.printStackTrace();
        }

        return operateSql.executeSql(sqlWord);
    }

    public SqlReturnModel insert(String tableName, JSONObject apiData){
        //组装insert语句
        String insertSql = "INSERT INTO " + tableName;
        String fields = "(";
        String data = "(";
        for (Map.Entry me: apiData.entrySet()){
            String k = String.valueOf(me.getKey());
            String v = String.valueOf(me.getValue());
            v = v.replace("'", "\\'");
            v = v.replace("\"", "\\\"");
            fields = fields + "`" + k + "`" + ",";
            data = data + "'" + v + "'" + ",";
        }
        fields = fields.substring(0, fields.length()-1);
        data = data.substring(0, data.length()-1);
        fields = fields + ")";
        data = data + ")";
        insertSql = insertSql + fields + "VALUES" + data;

        OperateSql operateSql = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSql.createConnection();
        }catch (Exception e){
            e.printStackTrace();
        }

//        System.out.println(insertSql);
        return operateSql.executeSql(insertSql);
    }

    //避免重复链接
    public SqlReturnModel updateEfficiency(String sql){
        return operateSqlFirst.executeSql(sql);
    }

    public SqlReturnModel update(String sql){
        OperateSql operateSql = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSql.createConnection();
        }catch (Exception e){
            e.printStackTrace();
        }

        return operateSql.executeSql(sql);
    }

    public SqlReturnModel update(String tableName, String set, String where){
        //UPDATE testcase SET isdel="1",projectName="test" WHERE id="12";
        //组装update sql语句
        String whereString = "";
        if (!Objects.equals(where, "")){
            whereString = " WHERE " + where;
        }
        String updateSql = "UPDATE " + tableName + " SET " + set + whereString;
        System.out.println(updateSql);

        OperateSql operateSql = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSql.createConnection();
        }catch (Exception e){
            e.printStackTrace();
        }

        return operateSql.executeSql(updateSql);
    }

    public SqlReturnModel update(String tableName, JSONObject apiData){
        //获取id，组装where值
        String id = apiData.getString("id");
        String where = " WHERE ID=\"" + id + "\"";
        apiData.remove("id");

        //组装update参数
        //UPDATE beingtestedapi SET	method="get",port_api="8081" WHERE id="1";
        String setData = " SET ";
        ArrayList<String> one = new ArrayList<String>();
        for (Map.Entry me: apiData.entrySet()) {
            String k = String.valueOf(me.getKey());
            String v = String.valueOf(me.getValue());
            one.add(k + "=" + "\'" + v + "\'");
        }
        setData = setData + String.join(",", one);

        //组装update sql语句
        String updateSql = "UPDATE " + tableName + setData + where;

        OperateSql operateSql = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSql.createConnection();
        }catch (Exception e){
            e.printStackTrace();
        }

        return operateSql.executeSql(updateSql);
    }

    //select查询
    public SqlReturnModel selectWord(String sqlWord){
        OperateSql operateSql = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSql.createConnection();

        }catch (Exception e){
            e.printStackTrace();
        }
        return operateSql.executeSql(sqlWord);
    }

    //倒叙，去重 查询被测接口版本号
    public SqlReturnModel select(String tableName){
        String sqlWord = "SELECT DISTINCT version FROM " + tableName;
        OperateSql operateSql = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSql.createConnection();
        }catch (Exception e){
            e.printStackTrace();
        }
        return operateSql.executeSql(sqlWord);
    }


    //根据定义where的条件查询
    public SqlReturnModel select(String tableName, String whereRight){
        String sqlWord = "SELECT * FROM " + tableName + " WHERE " + whereRight;
        OperateSql operateSql = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSql.createConnection();

        }catch (Exception e){
            e.printStackTrace();
        }
        return operateSql.executeSql(sqlWord);
    }

    public SqlReturnModel select(String tableName, String whereRight, String descField){
        String where = "";
        if (!Objects.equals(whereRight, ""))
            where =  " WHERE " + whereRight;
        String sqlWord = "SELECT * FROM " + tableName + where + " order by " + descField + " DESC;";
        OperateSql operateSql = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSql.createConnection();

        }catch (Exception e){
            e.printStackTrace();
        }
        return operateSql.executeSql(sqlWord);
    }

    public SqlReturnModel select(String selectTables, String tableName, String whereRight, String descField){
        String where = "";
        if (!Objects.equals(whereRight, ""))
            where =  " WHERE " + whereRight;
        String sqlWord = "SELECT " + selectTables + " FROM " + tableName + where + " order by " + descField + " DESC;";
        OperateSql operateSql = new OperateSql(myConfig.getPropertiesConfig().getProperties());
        try {
            operateSql.createConnection();
        }catch (Exception e){
            e.printStackTrace();
        }
        return operateSql.executeSql(sqlWord);
//        return myConfig.getOperateSql().executeSql(sqlWord);
    }
}